import re
import pymysql
import sys
import getopt

# mysqlbinlog工具解析binlog后生成的日志文件路径
file_name = ''
# 目标数据库名
target_database = ''
# 目标表名
target_table = ''

try:
    opts, args = getopt.getopt(sys.argv[1:],"hf:d:t:",["help","file=","database=","table="])
except getopt.GetoptError:
    print ('python binlog_to_sql.py --file=<file_generated_by_mysqlbinlog> --database=<database_name> --table=<table_name>\nor')
    print ('python binlog_to_sql.py -f <file_generated_by_mysqlbinlog> -d <database_name> -t <table_name>')
    sys.exit(2)
for opt, arg in opts:
    if opt in ('-h', '--help'):
        print('python binlog_to_sql.py --file=<file_generated_by_mysqlbinlog> --database=<database_name> --table=<table_name>\nor')
        print ('python binlog_to_sql.py -f <file_generated_by_mysqlbinlog> -d <database_name> -t <table_name>')
        sys.exit()
    elif opt in ('-f', '--file'):
        file_name = arg
    elif opt in ('-d', '--database'):
        target_database = arg
    elif opt in ('-t', '--table'):
        target_table = arg


# 打开数据库连接
conn = pymysql.connect(host='localhost',
                    user='root',
                    password='123456',
                    database=target_database)
cursor = conn.cursor()

# 表结构信息
table_schema_dict = {}

def get_sql(file_name: str, target_tb: str):
    "将原始binlog转为可读sql"
    global table_schema_dict
    global target_database
    output = open(file='./original_sql.sql', mode='w', encoding='utf-8')
    output_rollback = open(file='./rollback_sql.sql', mode='w', encoding='utf-8')
    
    # 如果未指定表名则解析所有表的binlog
    process_any_table = False
    if target_tb == None or target_tb == '':
        process_any_table = True

    segment_pattern = re.compile(r'(?<=BEGIN).*?(?=COMMIT)', re.DOTALL)   # 以BEGIN和COMMIT为边界来匹配SQL语句，非贪婪模式，re.DOTALL表示.可匹配换行符
    
    time_pattern=re.compile(r'#\d{6} \d{2}:\d{2}:\d{2}')    # 匹配SQL的执行时间
    
    SQL_pattern = re.compile(r'###.*?(?=# at)', re.DOTALL)  # 匹配带'###'的SQL，非贪婪模式
    
    db_table_pattern = re.compile(r'`' + target_database + r'`\.`(.*?)`')  # 如果没有表名则使用此模式匹配动态查找表名

    with open(file=file_name, mode='r', encoding='utf-8') as fp:
        content = fp.read()
        # 找到所有BEGIN...COMMIT片段
        result1 = segment_pattern.findall(content)
        if len(result1) == 0:
            print("未找到事务片段！")
            return
        for segment in result1:
            start_pos = 0
            output.write('#----------\nBEGIN\n')
            output_rollback.write('#----------\nBEGIN\n')
            while True:
                # 获取时间
                origin_time_str = time_pattern.search(string=segment, pos=start_pos).group(0)
                # 去除无用信息和前缀'###'
                strip = SQL_pattern.search(string=segment, pos=start_pos)
                # 及时退出循环
                if not strip:
                    break
                # 更新下一次匹配的起始位置
                start_pos = strip.end()
                # 获得无杂质只有占位符的SQL
                flag_filtered = strip.group(0).replace('\n', ' ').replace('###   ', '').replace('### ', '')

                
                if process_any_table:
                    target_tb = db_table_pattern.search(string=flag_filtered).group(1)
                    print('匹配得到的表名:', target_tb)

                # 获取表的字段信息
                if table_schema_dict.get(target_tb) == None:
                    cursor.execute(f"describe {target_tb}")
                    field_info_list = cursor.fetchall()
                    field_list = []
                    for field in field_info_list:
                        field_name = field[0]
                        field_list.append(field_name)
                    table_schema_dict[target_tb] = field_list
                    print('field_list:',field_list)
                
                operation_type = flag_filtered[0:6]
                print(operation_type)
                
                if operation_type == 'DELETE':
                    print('删除语句替换前:')
                    print(flag_filtered)
                    
                    fields = table_schema_dict.get(target_tb)
                    for i in range(len(fields)):
                        if i == 0:
                            new_str = fields[i] + '='
                        else:
                            new_str = 'and '+ fields[i] + '='
                        flag_filtered = flag_filtered.replace('@'+str(i+1)+'=', new_str)
                    
                    print('\n删除语句@标志位替换结果:')
                    print(flag_filtered)
                    
                elif operation_type == 'UPDATE':
                    print('更新语句替换前:')
                    print(flag_filtered)
                    
                    # 以SET关键字分割前后两部分标志位,where和set两部分的@标志位替换规则不同
                    where_and_set = flag_filtered.split('SET')
                    fields = table_schema_dict.get(target_tb)
                    for i in range(len(fields)):
                        if i == 0:
                            new_str_where = fields[i] + '='
                            new_str_set = fields[i] + '='
                        else:
                            new_str_where = 'and '+ fields[i] + '='
                            new_str_set = ', '+ fields[i] + '='
                        where_and_set[0] = where_and_set[0].replace('@'+str(i+1)+'=', new_str_where)
                        where_and_set[1] = where_and_set[1].replace('@'+str(i+1)+'=', new_str_set)
                    
                    flag_filtered = 'SET'.join(where_and_set)
                    
                    print('\n更新语句@标志位替换结果:')
                    print(flag_filtered)
                    
                elif operation_type == 'INSERT':
                    print('插入语句替换前:')
                    print(flag_filtered)
                    
                    fields = table_schema_dict.get(target_tb)
                    for i in range(len(fields)):
                        if i == 0:
                            new_str = fields[i] + '='
                        else:
                            new_str = ', '+ fields[i] + '='
                        flag_filtered = flag_filtered.replace('@'+str(i+1)+'=', new_str)
                        
                    print('\n插入语句@标志位替换结果:')
                    print(flag_filtered)
                
                print('-----------------------')
                # 保存解析出的SQL
                output.write(origin_time_str + '\n')
                output.write(flag_filtered + ';\n')
                
                # 生成回滚SQL
                rollback_sql = generate_rollback_sql(flag_filtered, operation_type)
                output_rollback.write(origin_time_str + '\n')
                output_rollback.write(rollback_sql + ';\n')
            output.write('COMMIT\n#----------\n\n')
            output_rollback.write('COMMIT\n#----------\n\n')
    output.close()
    output_rollback.close()

def generate_rollback_sql(sql_str: str, sql_type: str):
    """生成回滚SQL"""
    if sql_type == 'DELETE':
        rollback_sql = re.sub(pattern=r'DELETE {1,}FROM', repl='INSERT INTO', string=sql_str)
        rollback_sql = rollback_sql.replace('WHERE', 'SET').replace('and', ',')
        print('DELETE的回滚SQL:')
        print(rollback_sql)
        ...
    elif sql_type == 'UPDATE':
        condition_splits = sql_str.split('SET')
        condition_splits[0] = condition_splits[0].replace('WHERE', 'SET').replace('and', ',')
        condition_splits[1] = condition_splits[1].replace(',', 'and')
        rollback_sql = 'WHERE'.join(condition_splits)
        print('UPDATE的回滚SQL:')
        print(rollback_sql)
        ...
    elif sql_type == 'INSERT':
        rollback_sql = re.sub(pattern=r'INSERT {1,}INTO', repl='DELETE FROM', string=sql_str)
        rollback_sql = rollback_sql.replace('SET', 'WHERE').replace(',', 'and')
        print('INSERT的回滚SQL:')
        print(rollback_sql)
    return rollback_sql
            

def main():
    
    get_sql(file_name=file_name, target_tb=target_table)
    
    # 关闭操作游标和连接
    cursor.close()
    conn.close()


if __name__ == "__main__":
    main()
    
    
        
